﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

namespace ReportingSite.DAL
{
    public class Group : BaseDataAccess
    {
        public Group() { }

        private static Group _instance = null;
        public static Group Instance
        {
            get
            {

                if (_instance == null)
                {
                    _instance = new Group();
                }
                return _instance;
            }
        }


        //access all Group objects
        public DataSet GetGroupObjectAll()
        {
            throw new Exception("Not implemented yet");
        }

        //access Group object metrics by time range
        public Metrics GetGroupObjectMetricsByTimeRange(int Hours)
        {

            SqlParameter[] paramList = new SqlParameter[7];
            SqlParameter param;

            param = new SqlParameter("@Hours", SqlDbType.Int);
            param.Value = Hours;
            paramList[0] = param;

            param = new SqlParameter("@NumberOfChanges", SqlDbType.Int);
            param.Direction = ParameterDirection.Output;
            paramList[1] = param;

            param = new SqlParameter("@NumberOfCreates", SqlDbType.Int);
            param.Direction = ParameterDirection.Output;
            paramList[2] = param;

            param = new SqlParameter("@UserWithMostRequests", SqlDbType.VarChar, 250);
            param.Direction = ParameterDirection.Output;
            paramList[3] = param;

            param = new SqlParameter("@UserWithMostRequestsCount", SqlDbType.Int);
            param.Direction = ParameterDirection.Output;
            paramList[4] = param;

            param = new SqlParameter("@UserWithMostChanges", SqlDbType.VarChar, 250);
            param.Direction = ParameterDirection.Output;
            paramList[5] = param;

            param = new SqlParameter("@UserWithMostChangesCount", SqlDbType.Int);
            param.Direction = ParameterDirection.Output;
            paramList[6] = param;

            int ret = SqlHelper.ExecuteNonQuery(this.connectionString, CommandType.StoredProcedure, "dbo.GetGroupObjectMetricsByTimeRange", paramList);


            Metrics metrics = new Metrics();
            metrics.NumberOfChanges = (paramList[1].Value == DBNull.Value) ? 0 : Convert.ToInt32(paramList[1].Value);
            metrics.NumberOfCreates = (paramList[2].Value == DBNull.Value) ? 0 : Convert.ToInt32(paramList[2].Value);
            metrics.UserWithMostRequests = (paramList[3].Value == DBNull.Value) ? "" : paramList[3].Value.ToString();
            metrics.UserWithMostRequestsCount = (paramList[4].Value == DBNull.Value) ? 0 : Convert.ToInt32(paramList[4].Value);
            metrics.UserWithMostChanges = (paramList[5].Value == DBNull.Value) ? "" : paramList[5].Value.ToString();
            metrics.UserWithMostChangesCount = (paramList[6].Value == DBNull.Value) ? 0 : Convert.ToInt32(paramList[6].Value);

            return metrics;

        }

        //access Group object report by time range
        public DataTable GetGroupObjectReportByTimeRange(int Hours)
        {
            SqlParameter[] paramList = new SqlParameter[1];
            SqlParameter param;

            param = new SqlParameter("@Hours", SqlDbType.Int);
            param.Value = Hours;
            paramList[0] = param;

            DataSet ds = SqlHelper.ExecuteDataset(this.connectionString, CommandType.StoredProcedure, "dbo.GetGroupObjectReportByTimeRange", paramList);

            if (ds == null)
            {
                return null;
            }
            else if (ds.Tables.Count <= 0)
            {
                return null;
            }
            else if (ds.Tables[0].Rows.Count <= 0)
            {
                return null;
            }
            else
            {
                return ds.Tables[0]; 
            }
        }



        //access Group object search by time range for attribute
        public DataTable GetGroupObjectSearchByTimeRangeForAttribute(int Hours, string searchTerm1, string searchTerm2, string searchTerm3)
        {
            SqlParameter[] paramList = new SqlParameter[4];
            SqlParameter param;

            param = new SqlParameter("@Hours", SqlDbType.Int);
            param.Value = Hours;
            paramList[0] = param;

            param = new SqlParameter("@SearchTerm1", SqlDbType.VarChar, 100);
            param.Value = searchTerm1;
            paramList[1] = param;

            param = new SqlParameter("@SearchTerm2", SqlDbType.VarChar, 100);
            param.Value = searchTerm2;
            paramList[2] = param;

            param = new SqlParameter("@SearchTerm3", SqlDbType.VarChar, 100);
            param.Value = searchTerm3;
            paramList[3] = param;

            DataSet ds = SqlHelper.ExecuteDataset(this.connectionString, CommandType.StoredProcedure, "dbo.GetGroupObjectSearchByTimeRangeForAttribute", paramList);

            if (ds == null)
            {
                return null;
            }
            else if (ds.Tables.Count <= 0)
            {
                return null;
            }
            else if (ds.Tables[0].Rows.Count <= 0)
            {
                return null;
            }
            else
            {
                return ds.Tables[0];
            }
        }

        //access Group object search by time range for requestor 
        public DataTable GetGroupObjectSearchByTimeRangeForRequestor(int Hours, string searchTerm1, string searchTerm2, string searchTerm3)
        {
            SqlParameter[] paramList = new SqlParameter[4];
            SqlParameter param;

            param = new SqlParameter("@Hours", SqlDbType.Int);
            param.Value = Hours;
            paramList[0] = param;

            param = new SqlParameter("@SearchTerm1", SqlDbType.VarChar, 100);
            param.Value = searchTerm1;
            paramList[1] = param;

            param = new SqlParameter("@SearchTerm2", SqlDbType.VarChar, 100);
            param.Value = searchTerm2;
            paramList[2] = param;

            param = new SqlParameter("@SearchTerm3", SqlDbType.VarChar, 100);
            param.Value = searchTerm3;
            paramList[3] = param;

            DataSet ds = SqlHelper.ExecuteDataset(this.connectionString, CommandType.StoredProcedure, "dbo.GetGroupObjectSearchByTimeRangeForRequestor", paramList);

            if (ds == null)
            {
                return null;
            }
            else if (ds.Tables.Count <= 0)
            {
                return null;
            }
            else if (ds.Tables[0].Rows.Count <= 0)
            {
                return null;
            }
            else
            {
                return ds.Tables[0];
            }
        }

        //access Group object search by time range for target 
        public DataTable GetGroupObjectSearchByTimeRangeForTarget(int Hours, string searchTerm1, string searchTerm2, string searchTerm3)
        {
            SqlParameter[] paramList = new SqlParameter[4];
            SqlParameter param;

            param = new SqlParameter("@Hours", SqlDbType.Int);
            param.Value = Hours;
            paramList[0] = param;

            param = new SqlParameter("@SearchTerm1", SqlDbType.VarChar, 100);
            param.Value = searchTerm1;
            paramList[1] = param;

            param = new SqlParameter("@SearchTerm2", SqlDbType.VarChar, 100);
            param.Value = searchTerm2;
            paramList[2] = param;

            param = new SqlParameter("@SearchTerm3", SqlDbType.VarChar, 100);
            param.Value = searchTerm3;
            paramList[3] = param;

            DataSet ds = SqlHelper.ExecuteDataset(this.connectionString, CommandType.StoredProcedure, "dbo.GetGroupObjectSearchByTimeRangeForTarget", paramList);

            if (ds == null)
            {
                return null;
            }
            else if (ds.Tables.Count <= 0)
            {
                return null;
            }
            else if (ds.Tables[0].Rows.Count <= 0)
            {
                return null;
            }
            else
            {
                return ds.Tables[0];
            }
        }


        //access Group object search by time range for target 
        public DataTable GetGroupObjectSearchByTimeRangeForAll(int Hours, string searchTerm1, string searchTerm2, string searchTerm3)
        {
            SqlParameter[] paramList = new SqlParameter[4];
            SqlParameter param;

            param = new SqlParameter("@Hours", SqlDbType.Int);
            param.Value = Hours;
            paramList[0] = param;

            param = new SqlParameter("@SearchTerm1", SqlDbType.VarChar, 100);
            param.Value = searchTerm1;
            paramList[1] = param;

            param = new SqlParameter("@SearchTerm2", SqlDbType.VarChar, 100);
            param.Value = searchTerm2;
            paramList[2] = param;

            param = new SqlParameter("@SearchTerm3", SqlDbType.VarChar, 100);
            param.Value = searchTerm3;
            paramList[3] = param;

            DataSet ds = SqlHelper.ExecuteDataset(this.connectionString, CommandType.StoredProcedure, "dbo.GetGroupObjectSearchByTimeRangeForAll", paramList);

            if (ds == null)
            {
                return null;
            }
            else if (ds.Tables.Count <= 0)
            {
                return null;
            }
            else if (ds.Tables[0].Rows.Count <= 0)
            {
                return null;
            }
            else
            {
                return ds.Tables[0];
            }
        }

    }


   
}